﻿using System;
using System.Data;
using System.Data.SqlClient;

namespace ReportsFactory
{
    public partial class TableRelation : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (Request.QueryString["SrcTable"] == null || Request.QueryString["TgtTable"] == null)
                throw new ApplicationException("Source or Target table not found");

            string srcTable = Request.QueryString["SrcTable"];
            string tgtTable = Request.QueryString["TgtTable"];
            string fldName = "";

            if (Request.QueryString["FldName"] != null)
                fldName = Request.QueryString["FldName"];


            string[] srcTabs = srcTable.Split(new string[] { "---" }, StringSplitOptions.RemoveEmptyEntries);
            string[] tgtTabs = tgtTable.Split(new string[] { "---" }, StringSplitOptions.RemoveEmptyEntries);
            lblSourceTable.Text = srcTabs[0];
            lblTargetTable.Text = tgtTabs[0];

            using (SqlConnection sqlCon = new SqlConnection(srcTabs[1]))
            {
                sqlCon.Open();

                SqlCommand command = sqlCon.CreateCommand();
                command.CommandText = "select column_name as COLNAME from information_schema.columns where table_name ='" + GetTableName(lblSourceTable.Text) + "' order by column_name";
                DataTable tables = new DataTable();
                tables.Load(command.ExecuteReader(CommandBehavior.CloseConnection));
                ddlSourceColumn.DataSource = tables;
                ddlSourceColumn.DataTextField = "COLNAME";
                ddlSourceColumn.DataBind();

                if (command != null)
                    command.Dispose();
            }


            using (SqlConnection sqlCon = new SqlConnection(tgtTabs[1]))
            {
                sqlCon.Open();

                SqlCommand command = sqlCon.CreateCommand();
                command.CommandText = "select column_name as COLNAME from information_schema.columns where table_name ='" + GetTableName(lblTargetTable.Text) + "' order by column_name";
                DataTable tables = new DataTable();
                tables.Load(command.ExecuteReader(CommandBehavior.CloseConnection));
                ddlTargetColumn.DataSource = tables;
                ddlTargetColumn.DataTextField = "COLNAME";
                ddlTargetColumn.DataBind();

                if (command != null)
                    command.Dispose();
            }


            if (!string.IsNullOrWhiteSpace(fldName))
            {
                if (ddlSourceColumn.Items.FindByText(fldName) != null)
                    ddlSourceColumn.Items.FindByText(fldName).Selected = true;

                if (ddlTargetColumn.Items.FindByText(fldName) != null)
                    ddlTargetColumn.Items.FindByText(fldName).Selected = true;
            }
        }


        private string GetTableName(string str)
        {
            string[] fld = str.Split('.');
            return fld[fld.Length - 1];
        }
    }
}